Scenario

Little Lemon needs your help with managing their bookings. They need you to create stored procedures that they can invoke as required to add, update and delete bookings in their database.

Prerequisites

You should have a basic Bookings table in your Little Lemon database that’s linked to a Customers table. An example of these tables is illustrated below for reference. Your tables can look slightly different once the required relationship exists between the two.

Task 1

In this first task you need to create a new procedure called AddBooking to add a new table booking record.

The procedure should include four input parameters in the form of the following bookings parameters:

The screenshot below shows an example of the AddBooking statement:

Task 2

For your second task, Little Lemon need you to create a new procedure called UpdateBooking that they can use to update existing bookings in the booking table.

The procedure should have two input parameters in the form of booking id and booking date. You must also include an UPDATE statement inside the procedure.

The screenshot below shows an example of the UpdateBooking procedure in use.

Task 3

For the third and final task, Little Lemon need you to create a new procedure called CancelBooking that they can use to cancel or remove a booking.

The procedure should have one input parameter in the form of booking id. You must also write a DELETE statement inside the procedure.

When the procedure is invoked, the output result should be similar to the following screenshot:

Conclusion

You have helped Little Lemon to create the required stored procedures for their booking system. And you should now be able to create multiple stored procedures to add, update and delete records in a database.